{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ea5a74f5",
   "metadata": {},
   "source": [
    "## Using the ArcticDB DataFrame Database for Tick Storage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e8d1bd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "!conda install -c conda-forge arcticdb -y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0327b605",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display, Markdown\n",
    "import time\n",
    "import pytz\n",
    "import datetime as dt\n",
    "import pandas as pd\n",
    "import arcticdb as adb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36b9fbe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from thetadata import (\n",
    "    ThetaClient,\n",
    "    OptionRight,\n",
    "    StreamMsg,\n",
    "    StreamMsgType,\n",
    "    StreamResponseType,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d102a59",
   "metadata": {},
   "source": [
    "Initialize ArcticDB and create a library for trades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "373eafc6",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "arctic = adb.Arctic(\"lmdb://arcticdb_options\")\n",
    "lib = arctic.get_library(\"trades\", create_if_missing=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4de90d3",
   "metadata": {},
   "source": [
    "Function to get the trade datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "584a91f9",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def get_trade_datetime(today, ms_of_day):\n",
    "    return today + dt.timedelta(milliseconds=ms_of_day)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c297b5a",
   "metadata": {},
   "source": [
    "Function to get the number of days to expiration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7fd1dd5",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def get_days_to_expiration(today, expiration):\n",
    "    return (expiration - today).days"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b69590d9",
   "metadata": {},
   "source": [
    "Callback function to handle received messages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edb5d41f",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def callback(msg):\n",
    "    today = dt.datetime.now(pytz.timezone(\"US/Eastern\")).replace(\n",
    "        hour=0, minute=0, second=0, microsecond=0\n",
    "    )\n",
    "\n",
    "    if msg.type == StreamMsgType.TRADE:\n",
    "        trade_datetime = get_trade_datetime(today, msg.trade.ms_of_day)\n",
    "        expiration = pd.to_datetime(msg.contract.exp).tz_localize(\"US/Eastern\")\n",
    "        days_to_expiration = get_days_to_expiration(today, expiration)\n",
    "        symbol = msg.contract.root\n",
    "        trade = {\n",
    "            \"root\": symbol,\n",
    "            \"expiration\": expiration,\n",
    "            \"days_to_expiration\": days_to_expiration,\n",
    "            \"is_call\": msg.contract.isCall,\n",
    "            \"strike\": msg.contract.strike,\n",
    "            \"size\": msg.trade.size,\n",
    "            \"trade_price\": msg.trade.price,\n",
    "            \"exchange\": str(msg.trade.exchange.value[1]),\n",
    "            \"bid_size\": msg.quote.bid_size,\n",
    "            \"bid_price\": msg.quote.bid_price,\n",
    "            \"ask_size\": msg.quote.ask_size,\n",
    "            \"ask_price\": msg.quote.ask_price,\n",
    "        }\n",
    "        trade_df = pd.DataFrame(trade, index=[trade_datetime])\n",
    "        if symbol in lib.list_symbols():\n",
    "            lib.update(symbol, trade_df, upsert=True)\n",
    "        else:\n",
    "            lib.write(symbol, trade_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "787716a5",
   "metadata": {},
   "source": [
    "Function to start streaming all trades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4a62d6c",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def stream_all_trades():\n",
    "    client = ThetaClient(username=\"YOUR_USERNAME\", passwd=\"YOUR_PASSWORD\")\n",
    "\n",
    "    client.connect_stream(callback)\n",
    "\n",
    "    req_id = client.req_full_trade_stream_opt()\n",
    "\n",
    "    response = client.verify(req_id)\n",
    "\n",
    "    if client.verify(req_id) != StreamResponseType.SUBSCRIBED:\n",
    "        raise Exception(\"Unable to stream.\")\n",
    "\n",
    "    time.sleep(120)  # Stream for 2 minutes\n",
    "\n",
    "    print(\"Cancelling stream...\")\n",
    "\n",
    "    client.remove_full_trade_stream_opt()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1526620d",
   "metadata": {},
   "source": [
    "Start streaming trades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60c0ffc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "stream_all_trades()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57361adb",
   "metadata": {},
   "source": [
    "Defragment symbols if necessary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3c76b04",
   "metadata": {},
   "outputs": [],
   "source": [
    "for symbol in lib.list_symbols():\n",
    "    if lib.is_symbol_fragmented(symbol):\n",
    "        lib.defragment_symbol_data(symbol)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebf85f7c",
   "metadata": {},
   "source": [
    "List the symbols in the library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a041746b",
   "metadata": {},
   "outputs": [],
   "source": [
    "lib.list_symbols()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4faa28d9",
   "metadata": {},
   "source": [
    "Read all the data for one of the symbols (e.g., QQQ)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02c1a045",
   "metadata": {},
   "outputs": [],
   "source": [
    "qqq = lib.read(\"QQQ\").data\n",
    "display(qqq)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9680cbe",
   "metadata": {},
   "source": [
    "Use the query builder to find options with tight spread"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf9f802d",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = adb.QueryBuilder()\n",
    "filter = (q.ask_price - q.bid_price) < 0.05\n",
    "q = q[filter]\n",
    "data = lib.read(\"QQQ\", query_builder=q).data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b6172a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2e03d99",
   "metadata": {},
   "source": [
    "Use the query builder to find options with more than 1 day to expiration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65e2a490",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = adb.QueryBuilder()\n",
    "filter = q.days_to_expiration > 1\n",
    "q = q[filter].groupby(\"expiration\").agg({\"bid_size\": \"sum\", \"ask_size\": \"sum\"})\n",
    "data = lib.read(\"QQQ\", query_builder=q).data.sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61016253",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dec917e0",
   "metadata": {},
   "source": [
    "**Jason Strimpel** is the founder of <a href='https://pyquantnews.com/'>PyQuant News</a> and co-founder of <a href='https://www.tradeblotter.io/'>Trade Blotter</a>. His career in algorithmic trading spans 20+ years. He previously traded for a Chicago-based hedge fund, was a risk manager at JPMorgan, and managed production risk technology for an energy derivatives trading firm in London. In Singapore, he served as APAC CIO for an agricultural trading firm and built the data science team for a global metals trading firm. Jason holds degrees in Finance and Economics and a Master's in Quantitative Finance from the Illinois Institute of Technology. His career spans America, Europe, and Asia. He shares his expertise through the <a href='https://pyquantnews.com/subscribe-to-the-pyquant-newsletter/'>PyQuant Newsletter</a>, social media, and has taught over 1,000+ algorithmic trading with Python in his popular course **<a href='https://gettingstartedwithpythonforquantfinance.com/'>Getting Started With Python for Quant Finance</a>**. All code is for educational purposes only. Nothing provided here is financial advise. Use at your own risk."
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
